package fr.bordeaux1.planingdesetudiants.controler.db;

import java.io.File;
import java.sql.Statement;

import fr.bordeaux1.planingdesetudiants.controler.ParserTex;
import fr.bordeaux1.planingdesetudiants.controler.ParserXML;

public class DBOpener {
	private static final String ACTIVATION_CLES_ETRANGERES = "PRAGMA foreign_keys = ON; ";

	private static final String CREATION_UES_QUERY = " CREATE TABLE [ues] ( "
			+ "[code_ue] TEXT  UNIQUE NOT NULL, "
			+ "[libele_ue] TEXT NOT NULL );";

	private static final String CREATION_ENSEIGNANTS_QUERY = " CREATE TABLE [enseignants] ( "
			+ "[id_enseignant] INTEGER  NOT NULL PRIMARY KEY AUTOINCREMENT, "
			+ "[nom_enseignant] TEXT  NOT NULL, "
			+ "[prenom_enseignant] TEXT  NOT NULL, "
			+ "[disponibilite_enseignant] TEXT DEFAULT 'TTTTTTTTTTTTTTTTTTTT' NOT NULL ); ";
	
	private static final String CREATION_ETUDIANTS_QUERY = " CREATE TABLE [etudiants] ( "
			+ "[numero_etudiant] INTEGER  NOT NULL PRIMARY KEY, "
			+ "[nom_etudiant] TEXT  NOT NULL, "
			+ "[prenom_etudiant] TEXT  NOT NULL );";

	private static final String CREATION_COURS_QUERY = "CREATE TABLE [cours] ( "
			+ "[id_cours] INTEGER  PRIMARY KEY NOT NULL, "
			+ "[type_cours] TEXT  NOT NULL, "
			+ "[code_ue] TEXT NOT NULL, "
			+ "[id_enseignant] INTEGER, "
			+ "[creneau_cours] INTEGER DEFAULT '0' NOT NULL,"
			+ "[salle] TEXT NOT NULL, "
			+ "FOREIGN KEY (code_ue) REFERENCES ues(code_ue), "
			+ "FOREIGN KEY (id_enseignant) REFERENCES enseignants(id_enseignant));";

	private static final String CREATION_GROUPES_QUERY = "  CREATE TABLE [groupes] ( "
			+ "[id_groupe] INTEGER  NOT NULL PRIMARY KEY AUTOINCREMENT, "
			+ "[nom_groupe] TEXT  NOT NULL, "
			+ "[code_ue] TEXT  NOT NULL, "
			+ "FOREIGN KEY (code_ue) REFERENCES ues(code_ue) );";

	private static final String CREATION_COURS_GROUPES_QUERY = "CREATE TABLE [cours_groupes] ( "
			+ "[id_groupe] INTEGER  NOT NULL, "
			+ "[id_cours] INTEGER  NOT NULL, "
			+ "PRIMARY KEY ([id_groupe],[id_cours]),"
			+ "FOREIGN KEY (id_groupe) REFERENCES groupes(id_groupe), "
			+ "FOREIGN KEY (id_cours) REFERENCES cours(id_cours) ); ";

	private static final String CREATION_GROUPES_ETUDIANTS_QUERY = " CREATE TABLE [groupes_etudiants] ( "
			+ "[numero_etudiant] INTEGER  NOT NULL, "
			+ "[id_groupe] INTEGER  NOT NULL, "
			+ "PRIMARY KEY ([numero_etudiant],[id_groupe])"
			+ "FOREIGN KEY (numero_etudiant) REFERENCES etudiants(numero_etudiant),"
			+ "FOREIGN KEY (id_groupe) REFERENCES groupes(id_groupe) );";

	private static final String CREATION_UES_ETUDIANTS_QUERY = "CREATE TABLE [ues_etudiants] ( "
			+ "[numero_etudiant] INTEGER  NOT NULL, "
			+ "[code_ue] TEXT  NOT NULL, "
			+ "PRIMARY KEY ([numero_etudiant],[code_ue]), "
			+ "FOREIGN KEY (numero_etudiant) REFERENCES etudiants(numero_etudiant), "
			+ "FOREIGN KEY (code_ue) REFERENCES ues(code_ue) );";

	private static final String CREATION_IDX_UES = "CREATE INDEX [IDX_UES_] ON [ues]([code_ue]  ASC, "
			+ "[libele_ue]  ASC);";

	private static final String CREATION_IDX_NOM_ETUDIANTS = "CREATE INDEX [IDX_NOM_ETUDIANTS_] ON [etudiants]([nom_etudiant]  ASC)";

	public static IDBPlaningDesEtudiants open(String fichierdb3) {
		try {
			IDBPlaningDesEtudiants db = new DBPlaningDesEtudiantsSqlite(
					fichierdb3);
			return db;
		} catch (Exception e) {
			e.printStackTrace();
		}
		return null;
	}

	public static IDBPlaningDesEtudiants createFromXml(String fichierdb3,
			String fichierxml) throws Exception {
		DBPlaningDesEtudiantsSqlite db = new DBPlaningDesEtudiantsSqlite(
				fichierdb3);
		creerSchemaBd(db);
		ParserXML parser = new ParserXML();
		parser.parseXmlEtudiants(db, new File(fichierxml));
		return db;

	}
	public static IDBPlaningDesEtudiants create(String fichierdb3) throws Exception {
		DBPlaningDesEtudiantsSqlite db = new DBPlaningDesEtudiantsSqlite(
				fichierdb3);
		creerSchemaBd(db);
		return db;
	}
	public static IDBPlaningDesEtudiants createFromTex(String fichierdb3,
			String fichiertex) throws Exception {
		DBPlaningDesEtudiantsSqlite db = new DBPlaningDesEtudiantsSqlite(
				fichierdb3);
		creerSchemaBd(db);
		ParserTex parser = new ParserTex();
		parser.parseTexEtudiants(db, new File(fichiertex));
		return db;

	}

	private static void creerSchemaBd(DBPlaningDesEtudiantsSqlite db)
			throws Exception {
		Statement st = db.getConnection().createStatement();
		st.execute(ACTIVATION_CLES_ETRANGERES);
		st.execute(CREATION_UES_QUERY);
		st.execute(CREATION_ENSEIGNANTS_QUERY);
		st.execute(CREATION_ETUDIANTS_QUERY);
		st.execute(CREATION_COURS_QUERY);
		st.execute(CREATION_GROUPES_QUERY);
		st.execute(CREATION_COURS_GROUPES_QUERY);
		st.execute(CREATION_GROUPES_ETUDIANTS_QUERY);
		st.execute(CREATION_UES_ETUDIANTS_QUERY);
		st.execute(CREATION_IDX_UES);
		st.execute(CREATION_IDX_NOM_ETUDIANTS);
	}

}
